﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffIsNeedKey]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffIsNeedKey];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffIsNeedKey]
    @StaffType bit = 0
/*

====================================================
功能:    得到所有的人员
参数:
    @StaffType bit            :    员工类型
    
====================================================

*/
AS
BEGIN
SET NOCOUNT ON

--所有在职的
IF @StaffType=0
    SELECT
			a.*,
			(
				CASE 
					WHEN a.birthday is NULL then '-'
					WHEN datediff(dd,a.birthday,'1900-01-01')=0 then '-'
					ELSE convert(nvarchar,datediff(yy,a.birthday,getdate()))
				END
			) AS Age,
			(CASE sex WHEN 1 then N'男' ELSE N'女' END) AS SexName,
			Convert(nvarchar(10),A.RegistedDate,120) AS RQ,
			(SELECT Position_name FROM uds_Position WHERE Position_id = b.Position_id) AS Position_Name
        FROM 
            dbo.uds_staff a,
            dbo.uds_staff_in_position b
        WHERE
			a.Dimission = 0 
            and a.staff_id = b.staff_id
            and a.isneedkey = 0
        ORDER BY A.RegistedDate DESC

--所有不在职的
ELSE
    SELECT
			a.*,
			(
				CASE 
					WHEN a.birthday is NULL then '-'
					WHEN datediff(dd,a.birthday,'1900-01-01')=0 then '-'
					ELSE convert(nvarchar,datediff(yy,a.birthday,getdate()))
				END
			) AS Age,
			(CASE sex WHEN 1 then N'男' ELSE N'女' END) AS SexName,
			Convert(nvarchar(10),A.RegistedDate,120) AS RQ,
			(SELECT Position_name FROM uds_Position WHERE Position_id = b.Position_id) AS Position_Name
        FROM
			dbo.uds_staff a,
            dbo.uds_staff_in_position b
        WHERE
			a.Dimission = 0 
            and a.staff_id = b.staff_id
            and a.isneedkey = 1
        ORDER BY
			a.RegistedDate DESC

END
GO